IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetItemViaExternalID0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetItemViaExternalID0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetItems0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetItems0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPurgeCollection0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspPurgeCollection0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPurgeItem0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspPurgeItem0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspSaveItem0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspSaveItem0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspSaveItemIndexes0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspSaveItemIndexes0404]
GO



/****** Object:  StoredProcedure [dbo].[uspGetItemViaExernalID0404]    Script Date: 08/05/2010 14:10:16 ******/

CREATE PROCEDURE [dbo].[uspGetItemViaExternalID0404]
		@collectionPublishedID uniqueidentifier,
		@externalID NVARCHAR(200)
AS
BEGIN

	select * from Item with(nolock)
	where CollectionId = @collectionPublishedID AND 
	ExternalID = @externalID

END


GO



/****** Object:  StoredProcedure [dbo].[uspGetItems0404]    Script Date: 08/05/2010 14:10:16 ******/

CREATE PROCEDURE [dbo].[uspGetItems0404]
		@collectionPublishedID uniqueidentifier,
		@filterClause NVARCHAR(MAX),
		@pageNumber AS int,
		@pageSize AS int
AS
BEGIN

SET NOCOUNT ON;
DECLARE @BaseQuery NVARCHAR(MAX)
DECLARE @StartPosition int
DECLARE @EndPosition int

	SET @StartPosition = ((@pageNumber - 1) * @pageSize ) + 1
	SET @EndPosition = @pageNumber * @pageSize
	SET @filterClause = replace(@filterClause,'[@@COLUMN_NAME]','ObjectText')
	SET @BaseQuery ='
		WITH CollectionItems AS
		(
			SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber, ID
			FROM [Item] WITH (NOLOCK) ' 
              + @filterClause  
		+ ')

		--Return page
		SELECT i.*
		FROM CollectionItems ci WITH (NOLOCK)
			JOIN Item i WITH (NOLOCK) 
				ON ci.ID = i.ID
		WHERE 
			ci.RowNumber BETWEEN ' + CAST(@StartPosition AS NVARCHAR(12)) + ' AND ' + CAST(@EndPosition AS NVARCHAR(12)) + ' '
		+ char(13)
		
		--select @BaseQuery

		EXECUTE sp_executesql @BaseQuery
END


GO


/****** Object:  StoredProcedure [dbo].[uspPurgeCollection0404]    Script Date: 08/05/2010 14:10:28 ******/

-- =============================================
-- Author:		Patrick Thompson
-- Create date: 27 June 2008
-- Description:	Deletes all the items for a given collection
-- =============================================
CREATE PROCEDURE [dbo].[uspPurgeCollection0404] 
    @CollectionID uniqueidentifier
AS
BEGIN
	
	delete item where CollectionID = @CollectionID
		
END



GO


/****** Object:  StoredProcedure [dbo].[uspPurgeItem0404]    Script Date: 08/05/2010 14:10:37 ******/

-- =============================================
-- Author:		Patrick Thompson
-- Create date: 27 June 2008
-- Description:	Saves an Item in the item table
-- =============================================
CREATE PROCEDURE [dbo].[uspPurgeItem0404] 
	@PublishedID uniqueidentifier = NULL,
	@ExternalID nvarchar(255),
    @CollectionID uniqueidentifier
AS
BEGIN

	delete dbo.item where ExternalID = @ExternalID and CollectionID = @CollectionID
	--delete dbo.itemIndex where ItemPublishedID = @PublishedlID and CollectionID = @CollectionID
		
END


GO

/****** Object:  StoredProcedure [dbo].[uspSaveItem0404]    Script Date: 08/05/2010 14:10:47 ******/


-- =============================================
-- Author:		Patrick Thompson
-- Create date: 27 June 2008
-- Description:	Saves an Item in the item table
-- =============================================
CREATE PROCEDURE [dbo].[uspSaveItem0404] 
	@publishedID uniqueidentifier = NULL,
    @externalID nvarchar(255),
    @collectionID uniqueidentifier,
	@itemIsValid int,
    @itemStatusID bigint,
	@transactionID uniqueidentifier,
    @itemTypeVersion nvarchar(1024),
    @objectText nvarchar(max),
	@changeHistoryText nvarchar(max) = null,
	@StateMessage nvarchar(max) = null,
	@StatusMessage nvarchar(max) = null,
    @hashValue nvarchar(max)
AS
BEGIN

	-- local variables
    declare @itemID nvarchar(255)

	-- turn off counting
	SET NOCOUNT ON;

	-- determine if item already exists
    SELECT @itemID = ExternalID 
		FROM [Item]
		WHERE CollectionID = @collectionID
			and ExternalID = @externalID


--	If there is already an item with the given externalid, update that item 

    if (@itemID is not null) 
	BEGIN
		UPDATE [Item]
			SET ObjectText = @objectText,
				PublishedID = @publishedID,
				HashValue = @hashValue,
				ChangeHistoryText = @changeHistoryText,
				StatusID = @itemStatusID,
				ItemTypeVersion = @itemTypeVersion,
				ItemStatusID = @ItemIsValid + 1,
				StateMessage = @StateMessage,
				StatusMessage = @StatusMessage,
				LastModDate = getdate()
		WHERE ExternalID = @ExternalID
	END
    ELSE 
	BEGIN
		INSERT [Item]
			(PublishedID, ExternalID, ItemTypeVersion, CollectionID, StatusID, ItemStatusID, TransactionID,
			 ObjectText, ChangeHistoryText, HashValue, CreatedTransactionID, StateMessage, 
			 StatusMessage, EnteredDate, LastModDate)
		VALUES
			(@publishedID, @externalID, @itemTypeVersion, @collectionID, @itemStatusID, @ItemIsValid + 1, @transactionID,
			 @objectText, @changeHistoryText, @hashValue, @transactionID, @StateMessage, 
			 @StatusMessage, getdate(), getdate()) 
	END

	select @itemID as externalID
	
END

GO


-- Input should look something like 
--  <IndexEntriesList>
--		<IndexEntry><Pid>aPropertyName</Pid><Val>a property value</Val></IndexEntry>
--		<IndexEntry><Pid>anotherPropertyName</Pid><Val>another property value</Val></IndexEntry>
--	</IndexEntriesList>

CREATE PROCEDURE [dbo].[uspSaveItemIndexes0404](
	@ExternalID nvarchar(255),
    @CollectionID uniqueidentifier,
    @IndexData XML
    )
AS

--BEGIN TRANSACTION

--DELETE ItemIndex WHERE CollectionID = @CollectionID AND OwnerExternalID = @ExternalID

--DECLARE @TempIndexEntriesTable TABLE (Pid NVARCHAR(50), Val NVARCHAR(1024))      
--      INSERT INTO @TempIndexEntriesTable
--      SELECT  
--            TempInd.IndCol.value('Pid[1]','NVARCHAR(50)') AS Pid, 
--            TempInd.IndCol.value('Val[1]','NVARCHAR(1024)') AS Val 
--            FROM @IndexData.nodes('//IndexEntriesList/IndexEntry') AS TempInd(IndCol)

--INSERT ItemIndex (CollectionID, OwnerExternalID, PropertyName, PropertyValue)
--SELECT @CollectionID, @ExternalID, A.Pid, A.Val
--  FROM @TempIndexEntriesTable A
  
--COMMIT TRANSACTION

SELECT 'not implemented'

GO

